package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import controller.Course;
import controller.CourseSession;
import controller.Student;

public class Queries {
	/**
	 * 
	 * @param con
	 * @param username
	 * @return
	 * @throws SQLException
	 */
	public static boolean isStudentExists(Connection con,String username) throws SQLException{
		PreparedStatement stmt = con.prepareStatement("SELECT * FROM users WHERE username=?"); 
		stmt.setString(1,username);
		ResultSet rs = stmt.executeQuery();
		if (countRows(rs)!=0)
			return true;
		return false;
	}
	/**
	 * 
	 * @param con
	 * @param coursename
	 * @return
	 * @throws SQLException
	 */
	public static boolean isCourseExists(Connection con,String coursename) throws SQLException{
		PreparedStatement stmt = con.prepareStatement("SELECT * FROM courses WHERE coursename=?"); 
		stmt.setString(1,coursename);
		ResultSet rs = stmt.executeQuery();
		try{
			if (countRows(rs)!=0)
				return true;
			return false;
		}
		finally{
			if (stmt!=null){stmt.close();}
		}
		
	}
	/**
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static LinkedList<String> getStudentNames(Connection conn) throws SQLException{
		java.sql.Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE role='student';");
		if (!rs.first()){
			return null;
		}
		rs.beforeFirst();
		LinkedList<String> students = new LinkedList<String>();
		while (rs.next()){
			students.addLast(rs.getString("username"));
		}
		if(stmt!=null)
		{
			stmt.close();
		}
		return students;
	}
	/**
	 * 
	 * @param username
	 * @return
	 * @throws SQLException
	 */
	public static Student createStudent(Connection conn,String username) throws SQLException{
		
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE username=?"); 
		stmt.setString(1,username);
		ResultSet rs = stmt.executeQuery();
		if (countRows(rs)==0){
			return null;
		}
		rs.next();
		return new Student(rs.getString("username"),		
						   rs.getString("password"),
						   rs.getString("role"),
						   rs.getString("name"),
						   rs.getString("phone"),
						   registeredToCourses(username));
		
	}
	/**
	 * 
	 * @param username
	 * @return
	 * @throws SQLException
	 */
	public static LinkedList<Course> registeredToCourses(String username) throws SQLException{
		Connection conn=null;
		try{
			conn = Connect.getConnection();
			PreparedStatement stmt = conn.prepareStatement("SELECT * FROM registers WHERE username=?");
			stmt.setString(1,username);
			ResultSet rs = stmt.executeQuery();
			if (countRows(rs)==0){
				return null;
			}
			LinkedList<Course> courses = new LinkedList<Course>();
			rs.beforeFirst();
			while (rs.next()){
			    LinkedList<Course> s = getCourses(rs.getString("coursename"));
				courses.add(s.get(0));
			}
			return courses;
		} 
		catch (SQLException e){
			throw e;
		}
		finally{
			if(conn!=null)
			{
				conn.close();
			}
		}
		
	}
	/**
	 * 
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public static LinkedList<Course> getCourses(String name) throws SQLException
	{
		Connection conn = null;
		try{
			conn = Connect.getConnection();
			Statement stmt = conn.createStatement();
			Statement stmt2 = conn.createStatement();
			ResultSet rs=null;
			if(name!=null)
			{
				rs = stmt.executeQuery("SELECT * FROM courses WHERE coursename='"+name+"' ORDER BY group_number;");
			}
			else
			{
				rs = stmt.executeQuery("SELECT * FROM courses ORDER BY group_number;");
			}
			LinkedList<Course> courses = new LinkedList<Course>();
			rs.beforeFirst();
			while (rs.next()){
				ResultSet sessionRs=stmt2.executeQuery("SELECT * FROM sessions WHERE coursename='"+rs.getString("coursename")+"'");
				Course course=createCourse(rs,sessionRs,null);
				courses.add(course);
			}
			return courses;
		}
		catch(SQLException e)
		{throw e;}
		finally{
			if(conn!=null)
			{
				conn.close();
			}
		}
	}
	/**
	 * 
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public static LinkedList<Course> getUserCourses(Connection conn,String userName) throws SQLException
	{
		Student s=createStudent(conn,userName);
		LinkedList<Course> courses=getCourses(null);
		LinkedList<Course> userCourses=new LinkedList<Course>();
		for(int i=0;i<courses.size();i++)
		{
			if(s.canRegisterToCourse(courses.get(i)))
			{
				userCourses.addLast(courses.get(i));
			}
		}
		return userCourses;
	}
	/**
	 * 
	 * @param conn 
	 * @param groupNum
	 * @param username
	 * @return
	 * @throws SQLException
	 */
	public static LinkedList<Course> getCoursesFromGroup(Connection conn, Integer groupNum,String username) throws SQLException{
		Statement stmt = null,stmt2=null;
		try{
			 stmt = conn.createStatement();
			 stmt2 = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM courses WHERE group_number='"+groupNum+"';");
			LinkedList<Course> courses = new LinkedList<Course>();
			rs.beforeFirst();
			while (rs.next()){
				ResultSet sessionRs=stmt2.executeQuery("SELECT * FROM sessions WHERE coursename='"+rs.getString("coursename")+"'");
				Course course=createCourse(rs,sessionRs,username);
				courses.add(course);
			}
			return courses;
		}
		catch(SQLException e)
		{throw e;}
		finally{
			if(stmt!=null)
			{
				stmt.close();
			}
			if (stmt2!=null){
				stmt2.close();
			}
		}
	}
	
	/**
	 * 
	 * @param name
	 * @return
	 */
	public static long studentNumInCourse(String name)
	{
		Connection con=null;
		try {
			con=Connect.getConnection();
			PreparedStatement stmt=con.prepareStatement("" +
					"SELECT * FROM registers WHERE coursename=?");
			stmt.setString(1,name);
			ResultSet rs=stmt.executeQuery();
			return countRows(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
					//cant do nothing here
					return 0;
				}
		}
		finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
					//cant do nothing here
					return 0;
				}
				
		}
		return 0;
		
	}
	
	/**
	 * gets a course name and return all its sessions
	 * @param conn
	 * @param coursename
	 * @return
	 * @throws SQLException
	 */
	public static LinkedList<CourseSession> getCourseSessions(Connection conn,String coursename) throws SQLException{
		
		PreparedStatement stmt =null;
		try {
			stmt = conn.prepareStatement("SELECT * FROM sessions WHERE coursename=?");
			stmt.setString(1,coursename);
			ResultSet rs = stmt.executeQuery();
			LinkedList<CourseSession> sessions = new LinkedList<CourseSession>();
			rs.beforeFirst();
			while (rs.next()){
				CourseSession current = createSessionFromRs(rs,rs.getString("coursename"));
				sessions.add(current);
			}
			return sessions;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally {
			if(stmt!=null)
			{
				stmt.close();
			}
		}	
	}
	/**
	 * 
	 * @param rs
	 * @param sessionRs
	 * @param username
	 * @return
	 * @throws SQLException
	 */
	private static Course createCourse(ResultSet rs, ResultSet sessionRs,String username) throws SQLException {
		String name=rs.getString("coursename");
		String description=rs.getString("description");
		Float pointNum=rs.getFloat("points");
		Integer capacity=rs.getInt("capacity");
		Integer groupNum=rs.getInt("group_number");
		String creator;
		if(username!=null)
			creator=username;
		else
		{
			creator=rs.getString("creator");
		}
		CourseSession[] sessions=new CourseSession[countRows(sessionRs)];
		sessionRs.beforeFirst();
		int i=0;
		while(sessionRs.next())
		{
			sessions[i]=createSessionFromRs(sessionRs,name);
			i++;
		}
		Course course=new Course(name,description,pointNum,capacity,groupNum,creator,sessions);
		return course;
	}
	/**
	 * 
	 * @param rs
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	private static CourseSession createSessionFromRs(ResultSet rs,String name) throws SQLException {
		Integer start=rs.getInt("start");
		Integer end=rs.getInt("end");
		String day=rs.getString("day");
		CourseSession session=new CourseSession(start,end,day,name);
		return session;
	}
	/**
	 * moves the rs to before first and counts
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private static int countRows(ResultSet rs) throws SQLException {
		int i=0;
		rs.beforeFirst();
		while (rs.next()){
			i++;
		}
	rs.beforeFirst();
	return i;
}
	public static LinkedList<String> findCourses(Connection conn,
			String keywords, String points, String capacity, String group) throws SQLException {
		Statement checkStmt=conn.createStatement();
		ResultSet rs=checkStmt.executeQuery("SELECT * FROM courses");
		if(countRows(rs)<3)
		{
			LinkedList<String> list=new LinkedList<String>();
			rs.beforeFirst();
			while(rs.next())
			{
				list.add(rs.getString("coursename"));
			}
			return list;
		}
		String sqlQuery ="SELECT coursename,MATCH(coursename,description) AGAINST(?" +
				") AS score FROM courses WHERE MATCH(coursename,description)" +
				"AGAINST(? in boolean mode)";
		if (keywords==null || keywords.equals("") ){
			sqlQuery = "SELECT coursename FROM courses WHERE 1=1";
		}
		if (points.equals("under3")){
			sqlQuery+=" AND points < 3.0";
		}
		if (points.equals("over3")){
			sqlQuery+=" AND points >= 3.0";
		}
		if (capacity.equals("small")){
			sqlQuery+=" AND capacity < 10";
		}
		if (capacity.equals("medium")){
			sqlQuery+=" AND capacity >= 10 AND capacity <= 50";
		}
		if (capacity.equals("large")){
			sqlQuery+=" AND capacity > 50";
		}
		if ((group!=null) && (!group.equals(""))){
			sqlQuery+=" AND group_number = ?";
		}
		if (keywords!=null && !keywords.equals("")){
			sqlQuery+=" ORDER BY score DESC";
		}
		PreparedStatement stmt = conn.prepareStatement(sqlQuery);
		int index = 1; //for the group_number
		if (keywords!=null && !keywords.equals("")){
			stmt.setString(1,keywords);
			stmt.setString(2, keywords);
			index=3;
		}
		if ((group!=null) && (!group.equals(""))){
			stmt.setInt(index,Integer.parseInt(group));
		}
		rs = stmt.executeQuery();
		LinkedList<String> courses = new LinkedList<String>();
		rs.beforeFirst();
		while (rs.next()){
			courses.add(rs.getString("coursename"));
		}
		return courses;
	}
	
}
